﻿using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using Patent.DBUtility;

namespace Patent
{
    /// <summary>
    /// AIPC 的摘要说明
    /// </summary>
    public class AIPC
    {
        public AIPC()
        {
            //
            // TODO: 在此处添加构造函数逻辑
            //
        }
        public string CreateSql(string[] ipc, string tableName)
        {
            string sql = "";
            for (int i = 0; i < ipc.Length; i++)
            {
                if (string.IsNullOrEmpty(sql))
                {
                    sql = "select '" + ipc[i] + "' as 'ipc',substring([申请日],0,5) as 'year',count(*) as 'account' from " + tableName + " where [主分类号] like '" + ipc[i] + "%' group by substring([申请日],0,5) order by substring([申请日],0,5)";
                }
                else
                {
                    sql = sql + " ; " + "select '" + ipc[i] + "' as 'ipc',substring([申请日],0,5) as 'year',count(*) as 'account' from " + tableName + " where [主分类号] like '" + ipc[i] + "%' group by substring([申请日],0,5) order by substring([申请日],0,5)";
                }
            }
            return sql;
        }
        public DataSet GetIPCTrend(string[] ipc, string tableName)
        {
            DataSet data = new DataSet();
            SQLHelper helper = new SQLHelper();
            string sql = CreateSql(ipc, tableName);
            helper.RunSQL(sql, ref data);
            return data;
        }
        /// <summary>
        /// ipc趋势分析(为了grid)
        /// </summary>
        /// <param name="ipc">ipc类</param>
        /// <param name="tableName">专题</param>
        /// <param name="maxYear">最大年</param>
        /// <param name="minYear">最小年</param>
        /// <returns></returns>
        public DataTable GetTrend(string[] ipc, string tableName,string maxYear,string minYear)
        {
            DataTable dt = new DataTable();
            dt.Columns.Add("ipc");
            dt.Columns.Add("year");
            dt.Columns.Add("account");
            int countYear = Convert.ToInt32(maxYear) - Convert.ToInt32(minYear) + 1;//年份跨度
            string nowYear;//当前年

            SQLHelper helper = new SQLHelper();
            DataSet data = new DataSet();
            string sql = "";

            for (int i = 0; i < ipc.Length; i++)
            {
                nowYear = minYear;
                for (int j = 0; j < countYear; j++)
                {
                    nowYear = Convert.ToString(Convert.ToInt32(minYear) + j);
                    sql = "select '" + ipc[i] + "' as 'ipc', " + nowYear + " as 'year',count(*) as 'account' from " + tableName + " where [主分类号] like '" + ipc[i] + "%' and 申请日 like '" + nowYear + "%' ";
                    data.Clear();
                    helper.RunSqlUnclose(sql, ref data);
                    
                    dt.ImportRow(data.Tables[0].Rows[0]);
                }
            }
            
            helper.Close();
            return dt;
        }
        /// <summary>
        /// ipc趋势分析(为了chart)
        /// </summary>
        /// <param name="ipc">ipc类</param>
        /// <param name="tableName">专题</param>
        /// <param name="maxYear">最大年</param>
        /// <param name="minYear">最小年</param>
        /// <returns></returns>
        public DataTable GetTrendChart(string[] ipc, string tableName, string maxYear, string minYear)
        {
            DataTable dt = new DataTable(); 
            int countYear = Convert.ToInt32(maxYear) - Convert.ToInt32(minYear) + 1;//年份跨度
            string nowYear;//当前年
            
            dt.Columns.Add("year");
            for (int i = 0; i < countYear; i++)
            {
                dt.Rows.Add(Convert.ToString(Convert.ToInt32(minYear) + i).Substring(2,2));
            }
            for (int i = 0; i < ipc.Length; i++)
            {
                //dt.Columns.Add(ipc[i]);
                DataColumn colInt32 = new DataColumn(ipc[i]);
                colInt32.DataType = System.Type.GetType("System.Int32");
                dt.Columns.Add(colInt32);
            }

            SQLHelper helper = new SQLHelper();
            DataSet data = new DataSet();
            string sql = "";

            for (int i = 0; i < ipc.Length; i++)
            {
                nowYear = minYear;
                for (int j = 0; j < countYear; j++)
                {
                    nowYear = Convert.ToString(Convert.ToInt32(minYear) + j);
                    sql = "select '" + ipc[i] + "' as 'ipc', " + nowYear + " as 'year',count(*) as 'account' from " + tableName + " where [主分类号] like '" + ipc[i] + "%' and 申请日 like '" + nowYear + "%' ";
                    data.Clear();
                    helper.RunSqlUnclose(sql, ref data);

                    //dt.ImportRow(data.Tables[0].Rows[0]);
                    dt.Rows[j][ipc[i]] = Convert.ToInt32(data.Tables[0].Rows[0]["account"].ToString());
                }
            }

            helper.Close();
            return dt;
        }
    }
}
